from typing import Any, Dict, Optional, Union, List
from datetime import datetime

from sqlalchemy import select, and_, or_
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.risk import Risk, RiskComment
from app.models.issue import Issue, IssueComment
from app.schemas.risk_issue import (
    RiskCreate, RiskUpdate, RiskCommentCreate,
    IssueCreate, IssueUpdate, IssueCommentCreate
)


# 风险相关服务
async def get_risk_by_id(db: AsyncSession, *, id: int) -> Optional[Risk]:
    return await db.get(Risk, id)


async def get_risks(
    db: AsyncSession, *, skip: int = 0, limit: int = 100,
    project_id: Optional[int] = None, status: Optional[str] = None,
    severity: Optional[str] = None, assignee_id: Optional[int] = None
) -> List[Risk]:
    query = select(Risk)
    if project_id:
        query = query.where(Risk.project_id == project_id)
    if status:
        query = query.where(Risk.status == status)
    if severity:
        query = query.where(Risk.severity == severity)
    if assignee_id:
        query = query.where(Risk.assignee_id == assignee_id)
    query = query.offset(skip).limit(limit)
    result = await db.execute(query)
    return result.scalars().all()


async def create_risk(db: AsyncSession, *, obj_in: RiskCreate, creator_id: int) -> Risk:
    db_obj = Risk(
        title=obj_in.title,
        description=obj_in.description,
        status=obj_in.status,
        severity=obj_in.severity,
        probability=obj_in.probability,
        impact=obj_in.impact,
        mitigation_plan=obj_in.mitigation_plan,
        contingency_plan=obj_in.contingency_plan,
        project_id=obj_in.project_id,
        task_id=obj_in.task_id,
        assignee_id=obj_in.assignee_id,
        creator_id=creator_id,
    )
    db.add(db_obj)
    await db.commit()
    await db.refresh(db_obj)
    return db_obj


async def update_risk(
    db: AsyncSession, *, db_obj: Risk, obj_in: Union[RiskUpdate, Dict[str, Any]]
) -> Risk:
    if isinstance(obj_in, dict):
        update_data = obj_in
    else:
        update_data = obj_in.dict(exclude_unset=True)
    
    # 如果风险状态变为已关闭，自动设置关闭日期
    if update_data.get("status") == "closed" and not db_obj.closed_date:
        update_data["closed_date"] = datetime.utcnow()
    
    for field in update_data:
        setattr(db_obj, field, update_data[field])
    db.add(db_obj)
    await db.commit()
    await db.refresh(db_obj)
    return db_obj


async def delete_risk(db: AsyncSession, *, id: int) -> Risk:
    risk = await db.get(Risk, id)
    await db.delete(risk)
    await db.commit()
    return risk


async def create_risk_comment(
    db: AsyncSession, *, obj_in: RiskCommentCreate, creator_id: int
) -> RiskComment:
    db_obj = RiskComment(
        content=obj_in.content,
        risk_id=obj_in.risk_id,
        creator_id=creator_id,
    )
    db.add(db_obj)
    await db.commit()
    await db.refresh(db_obj)
    return db_obj


async def get_risk_comments(
    db: AsyncSession, *, risk_id: int, skip: int = 0, limit: int = 100
) -> List[RiskComment]:
    query = select(RiskComment).where(RiskComment.risk_id == risk_id)
    query = query.offset(skip).limit(limit)
    result = await db.execute(query)
    return result.scalars().all()


# 问题相关服务
async def get_issue_by_id(db: AsyncSession, *, id: int) -> Optional[Issue]:
    return await db.get(Issue, id)


async def get_issues(
    db: AsyncSession, *, skip: int = 0, limit: int = 100,
    project_id: Optional[int] = None, status: Optional[str] = None,
    priority: Optional[str] = None, assignee_id: Optional[int] = None
) -> List[Issue]:
    query = select(Issue)
    if project_id:
        query = query.where(Issue.project_id == project_id)
    if status:
        query = query.where(Issue.status == status)
    if priority:
        query = query.where(Issue.priority == priority)
    if assignee_id:
        query = query.where(Issue.assignee_id == assignee_id)
    query = query.offset(skip).limit(limit)
    result = await db.execute(query)
    return result.scalars().all()


async def create_issue(db: AsyncSession, *, obj_in: IssueCreate, creator_id: int) -> Issue:
    db_obj = Issue(
        title=obj_in.title,
        description=obj_in.description,
        status=obj_in.status,
        priority=obj_in.priority,
        due_date=obj_in.due_date,
        project_id=obj_in.project_id,
        task_id=obj_in.task_id,
        assignee_id=obj_in.assignee_id,
        creator_id=creator_id,
    )
    db.add(db_obj)
    await db.commit()
    await db.refresh(db_obj)
    return db_obj


async def update_issue(
    db: AsyncSession, *, db_obj: Issue, obj_in: Union[IssueUpdate, Dict[str, Any]]
) -> Issue:
    if isinstance(obj_in, dict):
        update_data = obj_in
    else:
        update_data = obj_in.dict(exclude_unset=True)
    
    # 如果问题状态变为已解决，自动设置解决日期
    if update_data.get("status") == "resolved" and not db_obj.resolved_date:
        update_data["resolved_date"] = datetime.utcnow()
    
    for field in update_data:
        setattr(db_obj, field, update_data[field])
    db.add(db_obj)
    await db.commit()
    await db.refresh(db_obj)
    return db_obj


async def delete_issue(db: AsyncSession, *, id: int) -> Issue:
    issue = await db.get(Issue, id)
    await db.delete(issue)
    await db.commit()
    return issue


async def create_issue_comment(
    db: AsyncSession, *, obj_in: IssueCommentCreate, creator_id: int
) -> IssueComment:
    db_obj = IssueComment(
        content=obj_in.content,
        issue_id=obj_in.issue_id,
        creator_id=creator_id,
    )
    db.add(db_obj)
    await db.commit()
    await db.refresh(db_obj)
    return db_obj


async def get_issue_comments(
    db: AsyncSession, *, issue_id: int, skip: int = 0, limit: int = 100
) -> List[IssueComment]:
    query = select(IssueComment).where(IssueComment.issue_id == issue_id)
    query = query.offset(skip).limit(limit)
    result = await db.execute(query)
    return result.scalars().all()